﻿using System;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using UFSoft.UBF.Business;
using UFSoft.UBF.Cust.ClassView.MVC.Query;
using UFSoft.UBF.PL;
using UFSoft.UBF.Sys.Database;
using UFSoft.UBF.Util.Context;
using UFSoft.UBF.Util.DataAccess;
using UFSoft.UBF.View.Query;

namespace UFSoft.UBF.Cust.ClassView.MVC.OQL
{
    /// <summary>
    ///     查询帮助类
    /// </summary>
    public static class QueryHelper
    {
        [Description("通过OQL查询")]
        public static QueryResult<DataTable> QueryByOQL(string oql, PageParamter pageParamter)
        {
            if (string.IsNullOrWhiteSpace(oql))
                throw new ArgumentException("oql cannot be empty", "oql");
            //替换上下文参数
            oql = ReplaceContextParameter(oql);
            QueryResult<DataTable> queryResult = new QueryResult<DataTable>();
            queryResult.PageParamter = pageParamter;
            DataSet dataSet;
            if (pageParamter == null)
            {
                EntityDataQuery entityDataQuery = new EntityDataQuery(string.Empty);
                entityDataQuery.QOptions.MultiLangOption = new GlobalizationContext(PlatformContext.Current.Culture);
                dataSet = entityDataQuery.FindDataSet(oql);
            }
            else
            {
                if (pageParamter.PageIndex < 0) pageParamter.PageIndex = 0;
                if (pageParamter.PageSize < 0) pageParamter.PageSize = 0;
                //通过Oql获取sql
                DataParamList dataParams = new DataParamList();
                string sql = ConvertOqlToSql(oql, ref dataParams);
                pageParamter.TotalCount = GetRecordCountBySql(sql);
                pageParamter.TotalPage = pageParamter.PageSize == 0
                    ? 0
                    : (long) Math.Ceiling((decimal) pageParamter.TotalCount / pageParamter.PageSize);
                try
                {
                    //获取分页DataSet
                    dataSet = GetDataSetByPage(sql, pageParamter.PageIndex * pageParamter.PageSize,
                        pageParamter.PageSize);
                }
                catch (SqlException sqlException)
                {
                    //分页异常，尝试不分页执行
                    queryResult.PageParamter = null;
                    EntityDataQuery entityDataQuery = new EntityDataQuery(string.Empty);
                    entityDataQuery.QOptions.MultiLangOption =
                        new GlobalizationContext(PlatformContext.Current.Culture);
                    dataSet = entityDataQuery.FindDataSet(oql);
                }
            }

            if (dataSet != null && dataSet.Tables.Count > 0)
            {
                DataTable table = dataSet.Tables[0];
                foreach (DataColumn column in table.Columns)
                {
                    if (column.ColumnName.StartsWith("A_"))
                        column.ColumnName = column.ColumnName.Substring(2);
                }

                queryResult.QueryData = table;
            }

            return queryResult;
        }

        /// <summary>
        ///     Oql转为Sql
        /// </summary>
        /// <param name="oql"></param>
        /// <returns></returns>
        public static string ConvertOqlToSql(string oql)
        {
            DataParamList dataParams = new DataParamList();
            //替换上下文参数
            oql = ReplaceContextParameter(oql);
            return ConvertOqlToSql(oql, ref dataParams);
        }

        /// <summary>
        ///     Oql转为Sql
        /// </summary>
        /// <param name="oql"></param>
        /// <param name="dataParams"></param>
        /// <returns></returns>
        public static string ConvertOqlToSql(string oql, ref DataParamList dataParams)
        {
            QueryContext queryContext = new QueryContext();
            ObjectQuery objectQuery = queryContext.DefineQuery(oql);
            CompiledQuery compiledQuery = objectQuery.CompiledQuery;
            OQLTools.MemoryEvaluate(compiledQuery.MemoryEvaluations, dataParams);
            return compiledQuery.SelectStatement;
        }

        /// <summary>
        ///     替换上下文参数
        /// </summary>
        /// <param name="oql"></param>
        /// <returns></returns>
        private static string ReplaceContextParameter(string oql)
        {
            if (string.IsNullOrEmpty(oql)) return oql;
            Regex r = new Regex("#Context\\.[^#]+?#", RegexOptions.IgnoreCase | RegexOptions.Compiled);
            MatchCollection matchCollection = r.Matches(oql);
            PlatformContext context = PlatformContext.Current;
            foreach (Match match in matchCollection)
            {
                string[] array = match.Value.Substring(1, match.Value.Length - 2).Split('.');
                if (array.Length != 2)
                    throw new ArgumentException("上下文参数结构异常");
                string parameterName = array[1];
                if (string.Compare(array[0], "Context", StringComparison.OrdinalIgnoreCase) != 0) continue;
                object parameterValue = context[parameterName];
                if (parameterValue == null || string.IsNullOrEmpty(parameterValue.ToString()))
                    throw new ArgumentException(string.Format("上下文参数:{0}不能为空值", parameterName));
                if (parameterValue is DateTime)
                    parameterValue = ((DateTime) parameterValue).ToString("yyyy-MM-dd HH:mm:ss");
                oql = oql.Replace(match.Value, parameterValue.ToString());
            }

            return oql;
        }

        #region 分页查询

        /// <summary>
        ///     获取分页DataSet
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="startRowIndex"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        private static DataSet GetDataSetByPage(string sql, long startRowIndex, long pageSize)
        {
            string selectsql;
            string wheresql;
            string ordersql;
            string tableName;
            //重建Sql分页
            ReBuildSqlForPage(ref pageSize, ref sql, out selectsql, out wheresql, out ordersql, out tableName);
            return GetDataSetByPage(tableName, pageSize, startRowIndex, selectsql, wheresql, ordersql, "", "*", null);
        }

        /// <summary>
        ///     获取分页DataSet
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="pageSize"></param>
        /// <param name="startRowIndex"></param>
        /// <param name="selectSql"></param>
        /// <param name="whereSql"></param>
        /// <param name="orderbySql"></param>
        /// <param name="optimizesql"></param>
        /// <param name="aliasFileds"></param>
        /// <param name="dataParams"></param>
        /// <returns></returns>
        private static DataSet GetDataSetByPage(string tableName, long pageSize, long startRowIndex, string selectSql,
            string whereSql, string orderbySql, string optimizesql, string aliasFileds, DataParamList dataParams)
        {
            string sql = CombineSqlForPage(tableName, pageSize, startRowIndex, selectSql, whereSql, orderbySql,
                aliasFileds);
            if (optimizesql != string.Empty)
            {
                sql = "with A as (" + sql + ") " + optimizesql;
            }

            DataSet dataSet;
            DataAccessor.RunSQL(DatabaseManager.GetCurrentConnection(), sql, dataParams, out dataSet);
            return dataSet;
        }

        /// <summary>
        ///     重建Sql分页
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="sql"></param>
        /// <param name="selectsql"></param>
        /// <param name="wheresql"></param>
        /// <param name="ordersql"></param>
        /// <param name="tableName"></param>
        private static void ReBuildSqlForPage(ref long pageSize, ref string sql, out string selectsql,
            out string wheresql, out string ordersql, out string tableName)
        {
            int fromIndex = sql.IndexOf(" from ", StringComparison.OrdinalIgnoreCase) + 1;
            int whereIndex = sql.IndexOf(" where ", StringComparison.Ordinal) + 1;
            int orderByIndex = sql.IndexOf(" order by ", StringComparison.Ordinal) + 1;
            if (whereIndex == 0)
            {
                if (orderByIndex > 0)
                {
                    sql = sql.Substring(0, orderByIndex) + " where 1=1 " + sql.Substring(orderByIndex);
                }
                else
                {
                    sql += " where 1=1";
                }
            }

            whereIndex = sql.IndexOf(" where ", StringComparison.Ordinal) + 1;
            if (orderByIndex == 0)
            {
                sql += " order by A.[ID]";
            }

            orderByIndex = sql.IndexOf(" order by ", StringComparison.Ordinal) + 1;
            selectsql = sql.Substring(7, fromIndex - 8);
            wheresql = sql.Substring(whereIndex + 6, orderByIndex - whereIndex - 7);
            ordersql = sql.Substring(orderByIndex + 9);
            tableName = sql.Substring(fromIndex + 5, whereIndex - fromIndex - 6);
        }

        /// <summary>
        ///     组合Sql分页
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="pageSize"></param>
        /// <param name="startRowIndex"></param>
        /// <param name="selectSql"></param>
        /// <param name="whereSql"></param>
        /// <param name="orderbySql"></param>
        /// <param name="aliasFileds"></param>
        /// <returns></returns>
        private static string CombineSqlForPage(string tableName, long pageSize, long startRowIndex, string selectSql,
            string whereSql, string orderbySql, string aliasFileds)
        {
            switch (pageSize)
            {
                case -1:
                    return "SELECT " + aliasFileds + " ,__Qry_RowNumber FROM (SELECT " + selectSql +
                           ", ROW_NUMBER() OVER(ORDER BY " + orderbySql + ") AS __Qry_RowNumber  FROM " + tableName +
                           " WHERE " +
                           whereSql + ") T";
                case 0:
                    throw new Exception(" pagesize 不能为 0 ");
                default:
                    long endRowIndex = startRowIndex + pageSize;
                    return "SELECT " + aliasFileds + " FROM ( SELECT " + selectSql + " , ROW_NUMBER() OVER(ORDER BY " +
                           orderbySql + " ) AS __Qry_RowNumber  FROM " + tableName + " WHERE " + whereSql +
                           ") T WHERE T.__Qry_RowNumber> " + startRowIndex + "  and T.__Qry_RowNumber<= " + endRowIndex;
            }
        }

        #endregion

        #region 获取实体数量

        /// <summary>
        ///     获取记录数量
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private static int GetRecordCountBySql(string sql)
        {
            DataParamList dataParams = new DataParamList();
            sql = GetRecordCountSQL(sql, dataParams);
            object obj;
            DataAccessor.RunSQL(DatabaseManager.GetCurrentConnection(), sql, dataParams, out obj);
            return (int) obj;
        }

        /// <summary>
        ///     获取记录数量Sql
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dataParams"></param>
        /// <returns></returns>
        private static string GetRecordCountSQL(string sql, DataParamList dataParams)
        {
            int orderByIndex = sql.IndexOf(" order by ", StringComparison.OrdinalIgnoreCase);
            if (orderByIndex > 0)
                sql = sql.Substring(0, orderByIndex);
            return string.Format("select count(*) as recordCount from ({0}) as A", sql);
        }

        #endregion
    }
}